﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DAL;
using System.Data.SqlClient;
using VOL;

namespace BLL
{
    public class QuyPhongBL
    {
        private DataAccess dataAccess;

        public QuyPhongBL()
        {
            dataAccess = new DataAccess();
        }
        /// <summary>
        /// 1. Hiển thị thông tin quỹ hiện tại theo năm đưa vào
        /// </summary>
        /// <returns></returns>
        public DataTable GetData(int nam)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@NAM", SqlDbType.Int) { Value = nam };

            // cau query select
            string sql = "SELECT * FROM tblQUYPHONG WHERE NAM = @NAM ";
            DataTable dt = dataAccess.GetData(sql, param);

            return dt;
        }

        /// <summary>
        /// 2. Thêm QUY PHONG
        /// </summary>
        /// <param name="obj">QUYPHONG</param>
        /// <returns>true</returns>
        public bool InsertData(QUYPHONG obj)
        {
            SqlParameter[] param = new SqlParameter[6];
            param[0] = new SqlParameter("@SoTienNamTruoc", SqlDbType.Float) { Value = obj.SoTienNamTruoc};
            param[1] = new SqlParameter("@SoTienThuCB", SqlDbType.Float) { Value = obj.SoTienThuCB };
            param[2] = new SqlParameter("@SoTienThuKhac", SqlDbType.Float) { Value = obj.SoTienThuKhac };
            param[3] = new SqlParameter("@SoTienChi", SqlDbType.Float) { Value = obj.SoTienChi };
            param[4] = new SqlParameter("@SoTienConLai", SqlDbType.Float) { Value = obj.SoTienConLai };
            param[5] = new SqlParameter("@Nam", SqlDbType.Int) { Value = obj.Nam};
            

            // cau query select
            string sql = "INSERT INTO tblQUYPHONG (SOTIENNAMTRUOC, SOTIENTHUCB, SOTIENTHUKHAC, SOTIENCHI, SOTIENCONLAI, NAM) VALUES (@SoTienNamTruoc, @SoTienThuCB, @SoTienThuKhac, @SoTienChi, @SoTienConLai, @Nam )";
            dataAccess.Execute(sql, param);

            return true;
        }

        /// <summary>
        /// 4. sửa QUY PHONG
        /// </summary>
        /// <param name="obj">QUYPHONG</param>
        /// <returns>true</returns>
        public bool UpdateData(QUYPHONG obj)
        {
            SqlParameter[] param = new SqlParameter[7];
            param[0] = new SqlParameter("@SoTienNamTruoc", SqlDbType.Float) { Value = obj.SoTienNamTruoc };
            param[1] = new SqlParameter("@SoTienThuCB", SqlDbType.Float) { Value = obj.SoTienThuCB };
            param[2] = new SqlParameter("@SoTienThuKhac", SqlDbType.Float) { Value = obj.SoTienThuKhac };
            param[3] = new SqlParameter("@SoTienChi", SqlDbType.Float) { Value = obj.SoTienChi };
            param[4] = new SqlParameter("@SoTienConLai", SqlDbType.Float) { Value = obj.SoTienConLai };
            param[5] = new SqlParameter("@Nam", SqlDbType.Int) { Value = obj.Nam };

            param[6] = new SqlParameter("@IDQUY", SqlDbType.Int) { Value = obj.IDQuy};
            // cau query select
            string sql = "UPDATE tblQUYPHONG SET SOTIENNAMTRUOC = @SoTienNamTruoc, SOTIENTHUCB = @SoTienThuCB, SOTIENTHUKHAC = @SoTienThuKhac, SOTIENCHI = @SoTienChi, SOTIENCONLAI = @SoTienConLai " +
                           " WHERE NAM = @Nam";
            dataAccess.Execute(sql, param);

            return true;
        }

        //5. lay so  tien con lai cua quy phong tuong ung nam dua vao
        public float GetSotienQPCLai(int nam)
        {
            float SotienQPCLai;

            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@NAM", SqlDbType.Int) { Value = nam };

            // cau query select
            string sql = "SELECT SOTIENCONLAI FROM tblQUYPHONG WHERE NAM = @NAM ";
            DataTable dt = dataAccess.GetData(sql, param);

            SotienQPCLai = float.Parse(dt.Rows[0]["SOTIENCONLAI"].ToString());
            return SotienQPCLai;
        }
    }
}
